ORACLE 12C新特性

您所在的位置:网站首页 oracle rownum分页慢 ORACLE 12C新特性

ORACLE 12C新特性

#ORACLE 12C新特性| 来源: 网络整理| 查看: 265

休假了,有时间测试下12C的新功能了,今天测试下分页查询。在12C之前,要想分页查询,我们通常会用下面的SQL。

SQL> select * 2 from (select a.*, rownum rn 3 from (select id, 4 username 5 from top_test) a 6 where rn 0;

在12C版本,推出了TOP N SQL新特性,只需要向下面这样简单的SQL就可以实现。

SQL> SELECT * FROM top_test order by id fetch first 5 rows only;

下面测试下这个功能,测试环境OEL 5.7,ORACLE 12.1.0.1。首先创建测试表。

[oracle@dbdream ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Sat Jan 18 08:37:43 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 413372416 bytes Fixed Size 2289016 bytes Variable Size 322962056 bytes Database Buffers 79691776 bytes Redo Buffers 8429568 bytes Database mounted. Database opened. SQL> select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs; CON_ID DBID NAME OPEN_MODE ---------- ---------- ---------- -------------------- 2 4063594514 PDB$SEED READ ONLY 3 1587020587 STREAM MOUNTED 4 676818932 WIND MOUNTED SQL> ALTER SESSION SET CONTAINER=stream; Session altered. SQL> show con_name CON_NAME ------------------------------ STREAM SQL> startup Pluggable Database opened. SQL> create user stream identified by stream default tablespace users; User created. SQL> grant dba to stream; Grant succeeded. SQL> conn stream/stream@localhost/stream Connected. SQL> show con_name CON_NAME ------------------------------ STREAM SQL> show user USER is "STREAM" SQL> create table top_test(id number,username varchar2(30)); Table created. SQL> insert into top_test select rownum,username from dba_users; 40 rows created. SQL> commit; Commit complete.

创建测试表后,测试分页查询功能。

SQL> SELECT * FROM top_test order by id fetch first 5 rows only; ID USERNAME ---------- ------------------------------------------------------------ 1 C##DBDREAM 2 STREAM 3 PDBADMIN 4 AUDSYS 5 GSMUSER SQL> SELECT * FROM top_test order by id offset 5 rows fetch next 5 rows only; ID USERNAME ---------- ------------------------------------------------------------ 6 WIND 7 SPATIAL_WFS_ADMIN_USR 8 SPATIAL_CSW_ADMIN_USR 9 APEX_PUBLIC_USER 10 SYSDG

很方便,在对比下传统分页查询和12C的分页查询执行计划的变化。

–传统SQL:

SQL> select * 2 from (select a.*, rownum rn 3 from (select id, 4 username 5 from top_test 6 order by id) a 7 where rownum 0; ID USERNAME RN ---------- ------------------------------------------------------------ ---------- 1 C##DBDREAM 1 2 STREAM 2 3 PDBADMIN 3 4 AUDSYS 4 5 GSMUSER 5 Execution Plan ---------------------------------------------------------- Plan hash value: 1572730458 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 215 | 3 (0)| 00:00:01 | |* 1 | VIEW | | 5 | 215 | 3 (0)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | VIEW | | 40 | 1200 | 3 (0)| 00:00:01 | |* 4 | SORT ORDER BY STOPKEY| | 40 | 1200 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | TOP_TEST | 40 | 1200 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------

–12C分页查询:

SQL> SELECT * FROM top_test order by id fetch first 5 rows only; ID USERNAME ---------- ------------------------------------------------------------ 1 C##DBDREAM 2 STREAM 3 PDBADMIN 4 AUDSYS 5 GSMUSER Execution Plan ---------------------------------------------------------- Plan hash value: 763208110 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 40 | 2240 | 3 (0)| 00:00:01 | |* 1 | VIEW | | 40 | 2240 | 3 (0)| 00:00:01 | |* 2 | WINDOW SORT PUSHED RANK| | 40 | 1200 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | TOP_TEST | 40 | 1200 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------

12C的分页查询,执行计划更简单,个人感觉很像使用分组函数的执行计划,据说效率要比传统的分页效率要好很多。

本文固定链接: http://www.dbdream.com.cn/2014/01/oracle-12c%e6%96%b0%e7%89%b9%e6%80%a7-%e5%88%86%e9%a1%b5%e6%9f%a5%e8%af%a2top-n-sql/ | 信春哥,系统稳,闭眼上线不回滚!



【本文地址】


今日新闻


推荐新闻


CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3